﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace FYPDemo
{
    public class VirtualCommunication
    {
        public static DataTable listForumCategory()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listForumCategory";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable getTitle(int category)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listForumTitle";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Category", SqlDbType.Int));
                cmd.Parameters["@Category"].Value = category;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return dt;
        }

        public static int addNewPost(string title, string creator, int category, string content)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "addNewPost";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Title", SqlDbType.VarChar, 50));
                cmd.Parameters["@Title"].Value = title;

                cmd.Parameters.Add(new SqlParameter("@Creator", SqlDbType.VarChar, 50));
                cmd.Parameters["@Creator"].Value = creator;

                cmd.Parameters.Add(new SqlParameter("@Category", SqlDbType.Int));
                cmd.Parameters["@Category"].Value = category;

                cmd.Parameters.Add(new SqlParameter("@Content", SqlDbType.VarChar, 8000));
                cmd.Parameters["@Content"].Value = content;

                result = cmd.ExecuteNonQuery();                
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return result;
        }

        public static DataTable listComment(string title)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listComment";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Title", SqlDbType.VarChar, 50));
                cmd.Parameters["@Title"].Value = title;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return dt;
        }

        public static int addComment(string title, string comment, string creator, string updatedBy)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "addComment";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Title", SqlDbType.VarChar, 50));
                cmd.Parameters["@Title"].Value = title;

                cmd.Parameters.Add(new SqlParameter("@Comment", SqlDbType.VarChar, 5000));
                cmd.Parameters["@Comment"].Value = comment;

                cmd.Parameters.Add(new SqlParameter("@Creator", SqlDbType.VarChar, 50));
                cmd.Parameters["@Creator"].Value = creator;

                cmd.Parameters.Add(new SqlParameter("@UpdateBy", SqlDbType.VarChar, 50));
                cmd.Parameters["@UpdateBy"].Value = updatedBy;

                result = cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return result;
        }

        public static int deleteComment(int ID, string user)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "DeleteComment";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                cmd.Parameters["@ID"].Value = ID;

                cmd.Parameters.Add(new SqlParameter("@User", SqlDbType.VarChar, 50));
                cmd.Parameters["@User"].Value = user;

                result = cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return result;
        }


        public static DataTable getCommentByID(int ID)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "getCommentByID";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                cmd.Parameters["@ID"].Value = ID;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return dt;
        }
    }
}